/* QAD_ProductivityByWorkCtrReport.p - PRODUCTIVITY BY Work Center REPORT     */
/* Copyright 1986-2011 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy reoprp42.p for use by QAD Reporting Framework.       */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}
{{&US_WO}woconsdf.i}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}

/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* TEMP-TABLE DEFINITION BLOCK */

define temp-table tt_op_hist no-undo like op_hist
   field achvd_rate     as   integer
   field std_rate       like tt_op_hist.achvd_rate
   field pcs_comp       like tt_op_hist.achvd_rate
   field tot_hours      as   decimal
   field std_hours      like tot_hours
   field down_hours     like tot_hours
   field prod_hours     like tot_hours
   field wr_desc        like wr_route.wr_desc
   field pt_desc1       like pt_mstr.pt_desc1
   field ptal_alt_part  like ptal_mstr.ptal_alt_part
   field emp_name       like emp_mstr.emp_name
   field wc_desc        like wc_mstr.wc_desc
.

define temp-table ttReportConditions no-undo
   field ordertype as   character
.

define temp-table ttReportOptions no-undo
   field include_nonmilestone like mfc_ctrl.mfc_logical
   field detail               like mfc_ctrl.mfc_logical
.

define dataset dsReportResults for tt_op_hist,
                                   ttReportConditions,
                                   ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}
{com/qad/shell/report/reports/QAD_HelperFunctions.i}

define variable vhDS as handle    no-undo.

/* g = global */
define variable g_break_expression as character no-undo
 initial " break by op_wkctr by op_mch by op_date by op_part by op_wo_op by op_emp ".
define variable g_calculate_field as integer no-undo.
define variable g_summary_field like g_calculate_field initial 1 no-undo.
define variable g_detail_field like g_calculate_field initial 2 no-undo.
define variable g_calc_with_setup as logical initial false no-undo.

empty temp-table tt_op_hist         no-error.
empty temp-table ttReportConditions no-error.
empty temp-table ttReportOptions    no-error.

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_ProductivityByItemReport.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.
   
   run SetFieldMetaParameter in ipReportHandle(
      "ttReportOptions",
      "detail",
      "DataFormat",
      GetLabel("SUMMARY/DETAIL")).

   run SetFieldMetaParameter in ipReportHandle(
      "ttReportOptions",
      "include_nonmilestone",
      "DataFormat",
      GetLabel("YES/NO")).
   
   define variable c-lngd-dataset like lngd_det.lngd_dataset
      initial "wo_mstr" no-undo.
   define variable order_type_key like lngd_key1
      initial "0" no-undo.
   define variable orderType as character no-undo.

   for first lngd_det where lngd_dataset = c-lngd-dataset
                        and lngd_field   = "orderType"
                        and lngd_lang    = global_user_lang
                        and lngd_key1    = order_type_key
                        no-lock:
   end. /* for first lngd_det where ... */

   if available lngd_det
      then orderType = lngd_key2.
      else orderType = "".

   run SetFieldMetaParameter in ipReportHandle(
      "ttReportConditions",
      "orderType",
      "DefaultValue",
      orderType).

END PROCEDURE. /* PROCEDURE ModifyMetaData */

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString   as character no-undo.
   define variable hMainQuery    as handle    no-undo.
   define query    MainQuery     for op_hist,ptal_mstr,wo_mstr.
   define buffer   ptalmstr      for ptal_mstr.

   /* l = local */
   define variable l_run_var              like wr_run                no-undo.
   define variable l_setup_var            like wr_setup              no-undo.
   define variable l_run_eff              like wr_run                no-undo.
   define variable l_setup_eff            like l_run_eff             no-undo.
   define variable l_desc1                like pt_desc1              no-undo.
   define variable l_setup_per            like wr_setup              no-undo.
   define variable l_std_run              like wr_run                no-undo.
   define variable l_tot_hours            as   decimal               no-undo.
   define variable l_std_hours            like l_tot_hours           no-undo.
   define variable l_prod_hours           like l_tot_hours           no-undo.
   define variable l_down_hours           like l_tot_hours           no-undo.
   define variable l_achvd_rate           as   integer               no-undo.
   define variable l_pcs_comp             like l_achvd_rate          no-undo.
   define variable l_std_rate             like l_achvd_rate          no-undo.
   define variable l_shift                like op_hist.op_shift      no-undo.
   define variable l_summary              like mfc_logical           no-undo.
   define variable l_include_nonmilestone like mfc_logical           no-undo.
   define variable l_stdhrs               as   decimal               no-undo.
   define variable l_dwn_hours            as   decimal               no-undo.
   define variable l_d_prod_hrs           as   decimal               no-undo.
   define variable l_d_prod_percent       as   decimal               no-undo.
   define variable l_d_util_percent       as   decimal               no-undo.
   define variable l_d_attain_percent     as   decimal               no-undo.
   define variable l_order_type_key       like lngd_key1             no-undo.
   define variable l_c_lngd_dataset       like lngd_det.lngd_dataset no-undo.
   define variable l_orderType            as   character             no-undo.

   /* s = sum */
   define variable s_op_qty_comp like op_hist.op_qty_comp no-undo.
   define variable s_prod_hours  like l_prod_hours        no-undo.
   define variable s_down_hours  like l_down_hours        no-undo.
   define variable s_std_hours   like l_std_hours         no-undo.
   define variable s_std_rate    like l_std_rate          no-undo.
 
   assign
      l_c_lngd_dataset       = "wo_mstr"
      l_orderType            = GetFilterValue(
         "ttReportConditions", "orderType", "")
      l_shift                = GetFilterValue(
         "tt_op_hist", "op_shift", "")
      l_summary              = IsTrueValue(
         "ttReportOptions", "detail", false)
      l_include_nonmilestone = IsTrueValue(
         "ttReportOptions", "include_nonmilestone", false)
      g_calculate_field      = if l_summary
                                then g_summary_field
                                else g_detail_field
   .

   for first lngd_det no-lock
      where lngd_dataset = l_c_lngd_dataset
        and lngd_field   = "orderType"
        and lngd_lang    = global_user_lang
        and lngd_key2    = l_orderType:
   end. /* for first lngd_det no-lock */

   if not available lngd_det
      then do:
      {{&US_BBI}pxmsg.i &MSGNUM=10346 &ERRORLEVEL=4}
      return.
   end. /* if not available lngd_det */

   l_order_type_key = lngd_key1.

   assign
      hMainQuery           = query MainQuery:handle.

   queryString = "for each op_hist no-lock where op_domain = "
                  + quoter(global_domain)
                  + " and ( ".

   run AddSomeConditions in reportHandle
      ("op_hist", "op_shift", input-output queryString).

   queryString = queryString
               + " (op_hist.op_shift = " + quoter(l_shift)
               + " or " + quoter(l_shift) + " = '') "
               + " and   (op_qty_comp  <> 0 "
               + "     or op_act_run   <> 0 "
               + "     or op_act_setup <> 0) "
               + " and ( "
               + string(l_include_nonmilestone)
               + " or op_milestone) "
               + " and op_type <> 'DOWN' ) ".

   if not IsConditionEmpty("tt_op_hist","ptal_alt_part")
   then do:
      queryString = queryString
                  + " , "
                  + " first ptal_mstr no-lock "
                  + " where ptal_mstr.ptal_domain = " + quoter(global_domain)
                  + "   and ptal_mstr.ptal_part   = op_hist.op_part "
                  + "   and ptal_mstr.ptal_active ".
      run AddAllConditions in reportHandle
         ( " ptal_mstr " , input-output queryString ).
   end. /* if not IsConditionEmpty(...) */
   else do:
      queryString = queryString
                  + " , "
                  + " first ptal_mstr no-lock ".
   end. /* else do: */

   if l_order_type_key <> {&MFG_TYPE_DISCRETE_AND_REPETITIVE}
   then do:
      queryString = queryString
                  + " , "
                  + " first wo_mstr no-lock "
                  + " where wo_mstr.wo_domain = " + quoter(global_domain)
                  + "   and wo_mstr.wo_lot    = " + quoter(l_order_type_key).
   end. /* if l_order_type_key <> {&...} */
   else do:
      queryString = queryString
                  + " , "
                  + " first wo_mstr no-lock ".
   end. /* else do: */

   queryString = queryString
               + g_break_expression
               + " : ".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:

      if hMainQuery:first-of(g_calculate_field) then do:
         assign
            s_op_qty_comp = 0
            s_prod_hours  = 0
            s_down_hours  = 0
            s_std_hours   = 0
         .
      end. /* if hMainQuery:first-of() */

      assign
         l_prod_hours  = if op_hist.op_type <> "DOWNTIME"
            then op_hist.op_act_run
            else 0
         l_down_hours  = if op_hist.op_type = "DOWNTIME"
            then op_hist.op_act_run
            else 0
         l_std_hours   = op_std_run    * op_qty_comp
         s_op_qty_comp = s_op_qty_comp + op_hist.op_qty_comp
         s_prod_hours  = s_prod_hours  + l_prod_hours
         s_down_hours  = s_down_hours  + l_down_hours
         s_std_hours   = s_std_hours   + l_std_hours
      .

      if hMainQuery:last-of(g_calculate_field)
      then do:

         for first ptalmstr no-lock
         where    ptalmstr.ptal_domain = global_domain
              and ptalmstr.ptal_part   = op_hist.op_part
              and ptalmstr.ptal_active:
         end. /* for first ptal_mstr no-lock */

         for first emp_mstr no-lock
         where    emp_mstr.emp_domain   = global_domain
              and emp_mstr.emp_addr     = op_hist.op_emp:
         end. /* for first emp_mstr no-lock */

         for first wr_route no-lock
         where    wr_route.wr_domain    = global_domain
              and wr_route.wr_lot       = op_hist.op_wo_lot
              and wr_route.wr_op        = op_hist.op_wo_op:
         end. /* for first wr_route no-lock */

         for first pt_mstr no-lock
         where    pt_mstr.pt_domain     = global_domain
              and pt_mstr.pt_part       = op_hist.op_part:
         end. /* for first pt_mstr no-lock */

         for first wc_mstr no-lock
         where    wc_mstr.wc_domain     = global_domain
              and wc_mstr.wc_wkctr      = op_hist.op_wkctr
              and wc_mstr.wc_mch        = op_hist.op_mch:
         end. /* for first wc_mstr no-lock */

         create tt_op_hist.
         buffer-copy op_hist to tt_op_hist no-error.
         assign
            tt_op_hist.achvd_rate     = if s_prod_hours <> 0
               then s_op_qty_comp / s_prod_hours
               else 0
            tt_op_hist.std_rate       = if s_std_hours <> 0
               then s_op_qty_comp / s_std_hours
               else 0
            tt_op_hist.pcs_comp       = s_op_qty_comp
            tt_op_hist.tot_hours      = s_prod_hours
            tt_op_hist.std_hours      = s_std_hours
            tt_op_hist.down_hours     = s_down_hours
            tt_op_hist.prod_hours     = s_prod_hours
            tt_op_hist.wr_desc        = wr_route.wr_desc
               when available wr_route
            tt_op_hist.pt_desc1       =
               pt_mstr.pt_desc1 + " " + pt_mstr.pt_desc2
               when available pt_mstr
            tt_op_hist.ptal_alt_part  = ptalmstr.ptal_alt_part
               when available ptalmstr
            tt_op_hist.emp_name       = emp_mstr.emp_name
               when available emp_mstr
            tt_op_hist.wc_desc        = wc_mstr.wc_desc
               when available wc_mstr
         .

      end. /* if hMainQuery:last-of(g_calculate_field) */

      hMainQuery:get-next().
   end. /* REPEAT QUERY */

   hMainQuery:query-close().
   delete object hMainQuery.
   phReportResults = dataset dsReportResults:handle.

END PROCEDURE.
